package com.gis.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.gis.bean.Lost;
import com.gis.dto.LostDto;
import com.gis.util.JDBCUtils;
import com.gis.util.PageResult;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;

public class LostDao {
	QueryRunner qr = new QueryRunner(JDBCUtils.getC3p0DateSource());
	private static final LostDao INSTANCE = new LostDao();
	
	private LostDao() {}
	
	public static LostDao getInstance() {
		return INSTANCE;
	}

	/**
	 * 新增Lost
	 * 
	 * @param lost
	 * @return
	 */
	public int add(Lost lost) {
		int i = 0;
		try {
			i = qr.execute(
					"insert into tb_lost(username, goods_type_id, lost_time, lng, lat, address, description, file_name) values(?,?,?,?,?,?,?,?)",
					lost.getUsername(), lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(), lost.getLat(),
					lost.getAddress(), lost.getDescription(), lost.getFileName());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	public List<LostDto> listAll() {
		List<LostDto> lostList = new ArrayList<LostDto>();
		String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id limit ?, ?";
		try {
			lostList = qr.query(sql, new BeanListHandler<LostDto>(LostDto.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lostList;
	}

	/**
	 * 条件分页
	 * @param params
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public PageResult<LostDto> page(Map<String, Object> params) {
		String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id";
		String totalSql = "select count(1) from tb_lost where 1=1";
		List<Object> objList = new ArrayList<Object>();
		for (String key : params.keySet()) {
			if (key.equals("goodsTypeId") && params.get(key) != null) {
				sql += " and goods_type_id = ?";
				totalSql += " and goods_type_id = ?";
				objList.add(params.get(key));
			} else if (key.equals("startDate") && params.get(key) != null) {
				sql += " and lost_time > ?";
				totalSql += " and lost_time > ?";
				objList.add(params.get(key));
			} else if (key.equals("endDate") && params.get(key) != null) {
				sql += " and lost_time < ?";
				totalSql += " and lost_time < ?";
				objList.add(params.get(key));
			}
			

		}
		
		Long total = 0L;
		try {
			total = qr.query(totalSql, ArrayUtil.toArray(objList, Object.class), new ScalarHandler<Long>(1));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		objList.add(params.get("offset"));
		objList.add(params.get("rows"));
		sql += " order by lost_time desc limit ?, ?";
		
		List<LostDto> lostList = new ArrayList<LostDto>();
		
		try {
			lostList = qr.query(sql, ArrayUtil.toArray(objList, Object.class),
					new BeanListHandler<LostDto>(LostDto.class));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		PageResult<LostDto> pageResult = new PageResult<LostDto>();
		pageResult.setRows(lostList);
		pageResult.setTotal(total);
		return pageResult;
	}

	/**
	 * 分页查询 寻物启事
	 * 
	 * @param pageSize
	 * @param pageNum
	 * @return
	 */
	public List<LostDto> page(int pageSize, int pageNum) {
		List<LostDto> lostList = new ArrayList<LostDto>();
		String sql = "select tb_lost.id, username, lng, lat,address, description, lost_time as lostTime, file_name as fileName, tb_goods_type.`name` as goodsType from tb_lost, tb_goods_type where tb_lost.goods_type_id = tb_goods_type.id limit ?, ?";

		try {

			lostList = qr.query(sql, new BeanListHandler<LostDto>(LostDto.class), (pageNum - 1) * pageSize, pageSize);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lostList;
	}

	/**
	 * 查询总数
	 * 
	 * @return
	 */
	public Long total() {
		Long count = 0L;
		try {
			count = qr.query("select count(1) from tb_lost", new ScalarHandler<Long>(1));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * get detail/description of the lost info by id
	 * @param id
	 * @return
	 */
	public String getDetail(Integer id) {
		String sql = "select description from tb_lost where id = ?";
		Map<String, Object> map = new HashMap<>();
		try {
			map = qr.query(sql, new MapHandler(), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return (String)map.get("description");
	}
	
	/**
	 * delete lost info by id
	 * @param id
	 * @return
	 */
	public int deleteLost(Integer id) {
		String sql = "delete from tb_lost where id = ?";
		int i = 0;
		try {
			i = qr.update(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * get lost by id
	 * @param id
	 * @return
	 */
	public Lost getLost(Integer id) {
		String sql = "select username, goods_type_id as goodsTypeId, lost_time as lostTime, lng, lat, address, description, file_name as fileName from tb_lost where id = ?";
		Lost lost = null;
		try {
			lost = qr.query(sql, new BeanHandler<Lost>(Lost.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lost;
	}
	
	/**
	 * update lost by id
	 * @param lost
	 * @return
	 */
	public int updateLost(Lost lost) {
		
		String sql = "";
		int i = 0;
		try {
			if (StrUtil.isEmpty(lost.getFileName())) {
				sql = "update tb_lost set goods_type_id=?, lost_time=?, lng=?, lat=?, address=?, description=? where id = ?";
				i = qr.update(sql, lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(), lost.getLat(), lost.getAddress(), lost.getDescription(), lost.getId());
			} else {
				sql = "update tb_lost set goods_type_id=?, lost_time=?, lng=?, lat=?, address=?, description=?, file_name=? where id = ?";
				i = qr.update(sql, lost.getGoodsTypeId(), lost.getLostTime(), lost.getLng(), lost.getLat(), lost.getAddress(), lost.getDescription(), lost.getFileName(), lost.getId());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
}
